﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;

namespace KPIS.DBM
{
    public sealed class DBManager : IDBManager, IDisposable
    {
        #region Field

        // currently non

        #endregion

        #region constructor

        public DBManager()
        {

        }

        public DBManager(DataProvider providerType)
        {
            this.ProviderType = providerType;
        }

        public DBManager(DataProvider providerType, string connectionString)
        {
            this.ProviderType = providerType;
            this.ConnectionString = connectionString;
        }

        #endregion

        #region Property

        public IDbConnection Connection { get; private set; }
        public IDbCommand Command { get; private set; }
        public IDbTransaction Transaction { get; private set; }
        public IDbDataParameter[] Parameters { get; private set; }
        public IDataReader DataReader { get; set; }
        public DataProvider ProviderType { get; set; } 
        public string LastestExecuteMethod { get; set; }
        public string LastestCommandText { get; set; }
        public string ConnectionString { get; set; }

        public string VersionReleaseNote
        {
            get { return @"Updated Version 1.0.0.3, on 17 October, 2013. Added features from original release.
- Add ToString() overridden to show query string and(if has) error while quering.
- Upgrade AddParameters method, to support SqlServer, change it to DbNull.Value when null value parameter has been added.
- Upgrade Mysql.Data reference(MySql Data Connector) version to 6.6.5.0.
- Add ExecuteDataTable, for easier use.
"; }
        }

        public string QueryString
        {
            get
            {
                string commandText = this.LastestCommandText ?? "";
                var param = this.Parameters;

                foreach (var dbDataParameter in param ?? new IDbDataParameter[0])
                {
                    var paramValue = dbDataParameter.Value;
                    string paramValueString = "";
                    if (paramValue == null || paramValue == DBNull.Value)
                    {
                        paramValueString = "NULL";
                    }
                    else
                    {
                        if (paramValue is DateTime?)
                        {
                            paramValueString = ((DateTime?) paramValue).Value.Year.ToString(new CultureInfo("en-US")) + "-"
                                               + ((DateTime?) paramValue).Value.Month + "-" + ((DateTime?) paramValue).Value.Day;
                        }
                        else
                        {
                            paramValueString = paramValue.ToString();
                        }
                        paramValueString = "'" + paramValueString + "'";
                    }
                    commandText = commandText.Replace(dbDataParameter.ParameterName, paramValueString);
                } 
                return commandText;
            }
        }


        #endregion

        #region Method Member

        public void Open()
        {
            Connection = DBManagerFactory.GetConnection(ProviderType);
            Connection.ConnectionString = this.ConnectionString;

            if (Connection.State != ConnectionState.Open)
                Connection.Open();

            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
        }

        public void Close()
        {
            if (Connection.State != ConnectionState.Closed)
                Connection.Close();
        }

        public void Dispose()
        {
            //GC.SuppressFinalize(this);
            this.Close();
            this.Command = null;
            this.Transaction = null;
            this.Connection = null;
        }

        public void CreateParameters(int paramsCount)
        {
            Parameters = new IDbDataParameter[paramsCount];
            Parameters = DBManagerFactory.GetParameters(this.ProviderType, paramsCount);
        }

        public void AddParameters(int index, string paramName, object objValue)
        {
            if (index < Parameters.Length)
            {
                Parameters[index].ParameterName = paramName;
                Parameters[index].Value = objValue ?? DBNull.Value;
            }
        }

        public void BeginTransaction()
        {
            if (this.Transaction == null)
                Transaction = DBManagerFactory.GetTransaction(Connection);

            this.Command.Transaction = Transaction;
        }

        public void CommitTransaction()
        {
            if (this.Transaction != null)
                this.Transaction.Commit();

            Transaction = null;
        }

        public void RollBackTransaction()
        {
            if (this.Transaction != null)
                this.Transaction.Rollback();

            Transaction = null;
        }

        public IDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
            this.Command.Connection = this.Connection;
            this.PrepareCommand(this.Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            this.DataReader = this.Command.ExecuteReader();
            this.Command.Parameters.Clear();

            return this.DataReader;
        }

        public void CloseReader()
        {
            if (this.DataReader != null)
                this.DataReader.Close();
        }

        private void AttachParameters(IDbCommand command, IEnumerable<IDbDataParameter> commandParameters)
        {
            foreach (IDbDataParameter idbParameter in commandParameters)
            {
                if ((idbParameter.Direction == ParameterDirection.InputOutput) && (idbParameter.Value == null))
                    idbParameter.Value = DBNull.Value;

                command.Parameters.Add(idbParameter);
            }
        }

        private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType,
                                    string commandText, IEnumerable<IDbDataParameter> commandParameters)
        {
            command.Connection = connection;
            command.CommandText = commandText;
            command.CommandType = commandType;

            if (transaction != null)
            {
                command.Transaction = transaction;
            }

            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
        }

        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            this.LastestExecuteMethod = "ExecuteNonQuery";
            this.LastestCommandText = commandText;

            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
            PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            int returnValue = Command.ExecuteNonQuery();
            Command.Parameters.Clear();

            return returnValue;
        }

        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            this.LastestExecuteMethod = "ExecuteScalar";
            this.LastestCommandText = commandText;

            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
            PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            object returnValue = Command.ExecuteScalar();
            Command.Parameters.Clear();

            return returnValue;
        }

        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            this.LastestExecuteMethod = "ExecuteDataSet";
            this.LastestCommandText = commandText;

            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
            PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
            dataAdapter.SelectCommand = Command;
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            Command.Parameters.Clear();

            return dataSet;
        }

        public DataTable ExecuteDataTable(CommandType commandType, string commandText)
        {
            this.LastestExecuteMethod = "ExecuteDataTable";
            this.LastestCommandText = commandText;

            this.Command = DBManagerFactory.GetCommand(this.ProviderType);
            PrepareCommand(Command, this.Connection, this.Transaction, commandType, commandText, this.Parameters);
            IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType);
            dataAdapter.SelectCommand = Command;
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            Command.Parameters.Clear();

            return dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
        }

        #endregion
    }
}